package edu.jhu.cs.oose.scheduleit.dao.impl;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateTemplate;

import edu.jhu.cs.oose.scheduleit.dao.ScheduleDao;
import edu.jhu.cs.oose.scheduleit.util.Filter;
import edu.jhu.cs.oose.scheduleit.util.FilterParser;
import edu.jhu.cs.oose.scheduleit.util.ScheduleType;
import edu.jhu.cs.oose.scheduleit.vo.Event;
import edu.jhu.cs.oose.scheduleit.vo.Schedule;
import edu.jhu.cs.oose.scheduleit.vo.User;
import edu.jhu.cs.oose.scheduleit.vo.UserSchedule;
/**
 * The implementation class of the ScheduleDao interface
 * @author oosegroup2
 *
 */
public class ScheduleDaoImpl implements ScheduleDao{
	
	private HibernateTemplate hibernateTemplate;

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
	
	@Override
	public void save(Schedule schedule){
		this.hibernateTemplate.saveOrUpdate(schedule);
	}
	
	@Override
	public void delete(Schedule schedule){
		if (schedule.getType() != ScheduleType.SUBSCRIBED){
			//Delete the record from the merged Schedules which use this schedule
			Session session = null;
			StringBuilder queryString = new StringBuilder("Delete from Schedule_Schedule where mergedSchedules_id =");		
			queryString.append(schedule.getId());
			try {			
				session = this.hibernateTemplate.getSessionFactory().openSession();
				SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
				sqlQuery.executeUpdate();
			} catch (HibernateException e) {
				throw e;
			} finally{
				queryString.setLength(0);
				if (session != null && session.isOpen()){
					session.close();
				}
			}
			//Delete the schedule object.
			this.hibernateTemplate.delete(schedule);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Schedule> get(User user, ScheduleType type, int offset, int limit){
		StringBuilder queryString = new StringBuilder("SELECT s.* from Schedule s inner join UserSchedule us on s.id = us.schedule_id where us.user_id = ");
		queryString.append(user.getId());
		if (type != ScheduleType.ALL){
			queryString.append(" and us.type = '");
			queryString.append(type.toString());
			queryString.append("'");
		}
		queryString.append(" Order by us.schedule_id Limit ");
		queryString.append(offset);
		queryString.append(",");
		if (limit <=0){
			queryString.append(Integer.MAX_VALUE);
		}else{
			queryString.append(limit);
		}
				
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.addEntity(Schedule.class);
			List<Schedule> result = sqlQuery.list();
			if (type != ScheduleType.ALL){
				for (Schedule s: result){
					s.setType(type);
				}
			}else{
				for (Schedule s: result){
					List<UserSchedule> tmp = this.hibernateTemplate.find(" from UserSchedule Where schedule_id ="+s.getId()+" and user_id ="+user.getId());
					s.setType(tmp.get(0).getType());
				}
			}
			return result;
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}
	
	public int count(User user, ScheduleType type){
		StringBuilder queryString = new StringBuilder("SELECT count(*) from Schedule sa inner join UserSchedule us on sa.id = us.schedule_id where us.user_id = ");
		queryString.append(user.getId());
		if (type != ScheduleType.ALL){
			queryString.append(" and us.type = '");
			queryString.append(type.toString());
			queryString.append("'");
		}
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			return Integer.parseInt(sqlQuery.list().get(0).toString());
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public Schedule get(User user, int scheduleID){
		
		StringBuilder queryString = new StringBuilder("SELECT s.* from Schedule s inner join UserSchedule us on s.id = us.schedule_id where us.user_id = ");
		queryString.append(user.getId());
		queryString.append(" and s.id = ");
		queryString.append(scheduleID);
				
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.addEntity(Schedule.class);
			List<Schedule> result = sqlQuery.list();
			if (result.size() == 1){
				List<UserSchedule> tmp = this.hibernateTemplate.find(" from UserSchedule Where schedule_id ="+scheduleID+" and user_id ="+user.getId());
				result.get(0).setType(tmp.get(0).getType());
				return result.get(0);
			}else{
				return null;
			}
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Schedule> get(List<Filter> filters, int offset, int limit){
		return this.hibernateTemplate.findByCriteria(FilterParser.parse(Schedule.class, filters), offset, limit);
	}

	@SuppressWarnings("unchecked")
	public int count(List<Filter> filters){
		DetachedCriteria criteria = FilterParser.parse(Schedule.class, filters);
		criteria.setProjection(Projections.count("id"));
		List result = this.hibernateTemplate.findByCriteria(criteria);
		return Integer.parseInt(result.get(0).toString());
	}
	
	@Override
	public void addEvent(Schedule schedule, Event event) {

		StringBuilder queryString = new StringBuilder("INSERT INTO Schedule_Event (schedule_id,events_id) values(");
		queryString.append(schedule.getId());
		queryString.append(", ");
		queryString.append(event.getId());
		queryString.append(")");
				
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
		
	}

	@Override
	public void removeEvent(Schedule schedule, Event event) {
		
		StringBuilder queryString = new StringBuilder("DELETE FROM Schedule_Event where events_id = ");
		queryString.append(event.getId());
		if (!event.getContainer().equals(schedule)){
			queryString.append(" and schedule_id = ");
			queryString.append(schedule.getId());
		}
		
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}
	
	
}
